import pandas as pd
import pymysql
import time

def get_last_path(path):
    return path.split('->')[-1]


def path_pro():
    newtime = time.strftime("%Y-%m-%d", time.localtime())
    conn = pymysql.connect(host='192.168.2.205', user='jinhuitao', passwd='jinhuitao_123456', db='sunway_db', port=3306,
                           charset='utf8')
    sql_date = """select a.newtime from (
    select  newtime, count(1) from amazon_class a
    group by newtime ) a order by a.newtime desc limit 2;"""
    df_time = pd.read_sql(sql_date, conn)
    date1 = list(df_time['newtime'])[0].strftime('%Y-%m-%d')
    date2 = list(df_time['newtime'])[1].strftime('%Y-%m-%d')
    sql = f"""select list_i  from amazon_class where newtime = '{date1}';"""
    sql2 = f"""select list_i  from amazon_class where newtime = '{date2}';"""
    # a = pd.read_excel(file1, header=None)
    a = pd.read_sql(sql, conn)
    a = a.drop_duplicates()
    a.reset_index(drop=True, inplace=True)
    a.columns = ['path']
    # print('a\n', a)

    # b = pd.read_excel(file2, header=None)
    b = pd.read_sql(sql2, conn)
    b = b.drop_duplicates()
    b.reset_index(drop=True, inplace=True)
    b.columns = ['path']
    # print('b\n', b)

    acode = list(set(a['path']).difference(set(b['path'])))
    # print(acode)
    left = a[a['path'].apply(lambda x: x in acode)]
    left = left.reset_index(drop=True)
    left['flag'] = '增加路径'
    left['last'] = left['path'].apply(lambda x: get_last_path(x))
    # print('left\n', left)
    # print('######################################################')
    # left.to_excel('left.xlsx', index=False)

    bcode = list(set(b['path']).difference(set(a['path'])))
    right = b[b['path'].apply(lambda x: x in bcode)]
    right = right.reset_index(drop=True)
    right['flag'] = '减少路径'
    # right['last'] = right['path'].apply(lambda x: get_last_path(x))
    # print('right\n', right)
    # print('######################################################')
    # right.to_excel('right.xlsx', index=False)

    last_str = list(left['last'])
    # print('last_str\n', len(last_str))
    # print(last_str)
    # print('######################################################')
    for i in range(len(last_str)):
        # print(i, last_str[i])
        if '++' in last_str[i]:
            print('++ exists!!!!')
        else:
            bb = a[a['path'].str.contains(fr'{last_str[i]}')]
            # print('len', len(bb))
            if len(bb) > 0:
                bb['la'] = 'flag'
                bb1 = bb[['la', 'path']].groupby(['la']). \
                    apply(lambda x: '\n'.join(x['path'])).reset_index()
                bb1.rename(columns={'la': 'flag', 0: 'path'}, inplace=True)
                str1 = str(list(bb1['path'])[0])
                left.loc[left['last'] == last_str[i], ['con']] = str1
                left.sort_values(by=['con'], na_position='first', ascending=True,
                                 inplace=True)
    # total = right.append(left)
    total = left.append(right)

    total.to_excel(f'C:/Users/Administrator/Desktop/本次任务执行时间{newtime}Compare.xlsx', index=False)


if __name__ == '__main__':
    path_pro()
